/*
 * Project Name spring-boot-tools
 * File Name CreateMysqlTable
 * Package Name com.huxiaosu.toole.autocreate.tables
 * Create Time 2024/7/11
 * Create by name：liujie
 */
package com.huxiaosu.tools.autocreate.tables;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.ClassScanner;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.huxiaosu.tools.autocreate.AutoCreateAware;
import com.huxiaosu.tools.autocreate.common.Constants;
import com.huxiaosu.tools.autocreate.common.DdlType;
import com.huxiaosu.tools.autocreate.config.AutoCreateTableProperties;
import com.huxiaosu.tools.autocreate.utils.FieldInfo;
import com.huxiaosu.tools.autocreate.utils.TableInfo;
import com.huxiaosu.tools.autocreate.utils.TableUtil;
import com.mysql.cj.jdbc.ConnectionImpl;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.BeanFactoryAware;
import org.springframework.context.EnvironmentAware;
import org.springframework.core.env.Environment;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import static com.baomidou.mybatisplus.core.toolkit.StringPool.*;

/**
 * 自动创建 MYSQL 表结构
 *
 * @author liujie
 * @date 2024/7/11 13:23
 */
@Slf4j
public class CreateMysqlTable extends AbstractCreateTable implements EnvironmentAware, BeanFactoryAware, AutoCreateAware {
    private Environment environment;
    private BeanFactory beanFactory;
    private final List<Connection> connectionList = new ArrayList<>();

    private boolean showSql = true;
    private String ddlAuto = DdlType.update.name();
    List<String> executeSql = new ArrayList<>();

    @Override
    public void execute() {
        AutoCreateTableProperties autoCreateTableProperties = beanFactory.getBean(AutoCreateTableProperties.class);
        // step1 获取配置的 entityPackage ; 配置文件中  entityPackages 优先级大于 entityPackage
        List<String> entityPackage = super.getEntityPackage(autoCreateTableProperties);

        List<AutoCreateTableProperties.DataSourceConfig> datasource = autoCreateTableProperties.getDatasource();
        if (CollUtil.isEmpty(datasource)) {
            log.warn("<没有配置，基础信息;按照默认配置执行>");
            datasource = new ArrayList<>();
            AutoCreateTableProperties.DataSourceConfig springBootDatabase = super.getSpringBootDatabase(environment);
            datasource.add(springBootDatabase);
        }
        datasource.forEach(item -> {
            // 开始执行 每一个 datasource 配置
            showSql = item.isShowSql();
            ddlAuto = item.getDdlAuto().name();
            if (StringUtils.isBlank(ddlAuto)) {
                log.warn("没有配置自动建表");
                return;
            }
            if (!supportedDdlTypes.contains(ddlAuto)) {
                log.warn("auto.create.table.ddl-auto 配置错误，当前配置只支持[none/update/create]三种类型!");
                return;
            }
            this.initConnection(item.getDatasource());
        });

        // 获取所有的 class
        Set<Class<?>> classes = new HashSet<>(entityPackage.size());
        for (String itemPackage : entityPackage) {
            classes.addAll(ClassScanner.scanPackageByAnnotation(itemPackage, TableName.class));
        }
        List<String> allTableName = new ArrayList<>(classes.size());
        // 初始化用于存储各种操作表结构的容器
        // 使用 List 存储表名；如果遇到重复的则抛出异常；
        connectionList.forEach(connection -> {
            Map<String, Map<String, TableInfo>> baseTableMap = initTableMap();
            // 多数据源 循环创建
            for (Class<?> clItem : classes) {
                String tableName = TableUtil.getTableName(clItem);
                if (allTableName.contains(tableName)) {
                    String errorMsg = clItem.getName() + "#" + tableName + "表名出现重复，禁止创建！";
                    throw new RuntimeException(errorMsg);
                }
                allTableName.add(tableName);
                // 构建出全部表的增删改的map
                this.buildTableMapConstruct(clItem, baseTableMap, tableName, connection);
            }
            this.createOrModifyTableConstruct(baseTableMap);
            this.executeSql(connection);
            this.clearSql();
        });
    }

    private void createOrModifyTableConstruct(Map<String, Map<String, TableInfo>> baseTableMap) {
        // 创建新表
        createTableByMap(baseTableMap.get(Constants.NEW_TABLE_MAP));
        // 添加新的字段
        addFieldsByMap(baseTableMap.get(Constants.ADD_TABLE_MAP));

        // 修改字段类型等
        modifyFieldsByMap(baseTableMap.get(Constants.MODIFY_TABLE_MAP));
        // 删除字段
        removeFieldsByMap(baseTableMap.get(Constants.REMOVE_TABLE_MAP));
    }

    private void createTableByMap(Map<String, TableInfo> newTableMap) {
        // 做创建表操作
        if (!newTableMap.isEmpty()) {
            newTableMap.forEach((dbName, tableInfo) -> {
                String[] split = dbName.split("#");
                executeSql.add(this.createTableSql(split[0], tableInfo));
            });
        }
    }

    private void addFieldsByMap(Map<String, TableInfo> addTableMap) {
        if (!addTableMap.isEmpty()) {
            addTableMap.forEach((dbName, tableInfo) -> {
                String[] split = dbName.split("#");
                tableInfo.getFieldInfoList().forEach(dItem -> executeSql.add(this.addTableColumnSql(split[0], tableInfo.getTableName(), dItem)));
            });
        }
    }

    private void removeFieldsByMap(Map<String, TableInfo> removeTableMap) {
        // 做删除字段操作
        if (!removeTableMap.isEmpty()) {
            removeTableMap.forEach((dbName, tableInfo) -> {
                String[] split = dbName.split("#");
                List<String> allColumn = tableInfo.getFieldInfoList().stream().map(FieldInfo::getColumn).toList();
                allColumn.forEach(dItem -> executeSql.add(this.deleteTableColumnSql(split[0], tableInfo.getTableName(), dItem)));

            });
        }
    }

    private void modifyFieldsByMap(Map<String, TableInfo> modifyTableCommentMap) {
        if (!modifyTableCommentMap.isEmpty()) {
            modifyTableCommentMap.forEach((dbName, tableInfo) -> {
                String[] split = dbName.split("#");
                tableInfo.getFieldInfoList().forEach(dItem -> executeSql.add(this.modifyTableColumnSql(split[0], tableInfo.getTableName(), dItem)));

            });
        }
    }

    @SuppressWarnings("all")
    @SneakyThrows(value = SQLException.class)
    private void executeSql(Connection connection) {
        if (CollUtil.isEmpty(executeSql)) {
            return;
        }
        for (String sql : executeSql) {
            if (showSql) {
                log.info("<executeSql = \n {}> ", sql.replace(RETURN,"\n"));
            }
            connection.prepareStatement(sql).execute();
        }
    }

    private void clearSql() {
        executeSql = new ArrayList<>();
    }

    /**
     * 构建出全部表的增删改的map
     *
     * @param clas         package中的model的Class
     * @param baseTableMap 用于存储各种操作表结构的容器
     * @param tableName    表名
     */
    @SneakyThrows(value = Throwable.class)
    private void buildTableMapConstruct(Class<?> clas, Map<String, Map<String, TableInfo>> baseTableMap,
                                        String tableName, Connection connection) {
        String schema = ((ConnectionImpl) connection).getDatabase();
        List<FieldInfo> allFieldList = TableUtil.getAllFields(clas);
        if (allFieldList.isEmpty()) {
            log.warn("{}-{} 中不存在表字段,请检查", clas.getName(), tableName);
            return;
        }
        if (DdlType.create.name().equals(ddlAuto)) {
            // 删除当前表； 重新创建
            String dropTableSql = this.getDropTableSql(schema, tableName);
            if (showSql) {
                log.info("< drop table = {}>", dropTableSql);
            }
            executeSql.add(dropTableSql);
        }
        // 提取表的描述
        String tableComment = TableUtil.getTableComment(clas);

        // 检查表是否存在数据库；存在检查是不是需要更新；不存在直接创建
        String checkTableSql = this.checkTableSql(schema, tableName);
        String checkTableColumnSql = this.getTableColumnSql(schema, tableName);

        if (showSql) {
            log.info("< check table =  {}>", checkTableSql);
        }
        boolean tableExist = this.checkSchemaTable(checkTableSql, connection);
        // 不存在 直接创建
        Map<String, Object> map = new HashMap<>(2);
        map.put(Constants.TABLE_COLLATION_KEY, TableUtil.getTableDefaultChat());
        map.put(Constants.TABLE_ENGINE_KEY, TableUtil.getTableEngine());
        String key = schema + "#" + tableName;
        if (tableExist) {
            // 存在；检查字段是不是需要更新
            // 查询出来表所有的字段
            if (showSql) {
                log.info("< check table column =  {}>", checkTableColumnSql);
            }
            List<FieldInfo> fieldInfoList = this.findTableInfo(checkTableColumnSql, connection);
            List<String> dbAllColumn = fieldInfoList.stream().map(FieldInfo::getColumn).toList();
            if (showSql) {
                log.info("{} 已存在的字段 {}", tableName, dbAllColumn);
                List<String> collect = allFieldList.stream().map(FieldInfo::getColumn).toList();
                log.info("entity中字段 {}", collect);
            }
            // 判断新增字段
            List<FieldInfo> addFieldList = TableUtil.getAddFieldList(allFieldList, dbAllColumn);
            if (CollUtil.isNotEmpty(addFieldList)) {
                baseTableMap.get(Constants.ADD_TABLE_MAP).put(key, new TableInfo(addFieldList, tableName, tableComment));
            }
            // 判断删除字段
            List<FieldInfo> removeFieldList = TableUtil.getRemoveFieldList(allFieldList, dbAllColumn);
            if (CollUtil.isNotEmpty(removeFieldList)) {
                baseTableMap.get(Constants.REMOVE_TABLE_MAP).put(key, new TableInfo(removeFieldList, tableName, tableComment));
            }
            // 判断更新类型的字段
            List<FieldInfo> modifyFieldList = TableUtil.getModifyFieldList(fieldInfoList, allFieldList);
            if (CollUtil.isNotEmpty(modifyFieldList)) {
                baseTableMap.get(Constants.MODIFY_TABLE_MAP).put(key, new TableInfo(modifyFieldList, tableName, tableComment));
            }
        } else {
            // 表不存在，
            baseTableMap.get(Constants.NEW_TABLE_MAP).put(key, new TableInfo(allFieldList, map, tableName, tableComment));
        }
    }

    @SneakyThrows(value = SQLException.class)
    private boolean checkSchemaTable(String sql, Connection connection) {
        ResultSet resultSet = connection.createStatement().executeQuery(sql);
        if (resultSet.next()) {
            String schema = resultSet.getString("table_schema");
            String tableName = resultSet.getString("table_name");
            return StringUtils.isNotBlank(schema) && StringUtils.isNotBlank(tableName);
        }
        return false;
    }

    @SneakyThrows(value = SQLException.class)
    private List<FieldInfo> findTableInfo(String sql, Connection connection) {
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        ResultSet resultSet = connection.createStatement().executeQuery(sql);
        while (resultSet.next()) {
            String columnName = resultSet.getString("column_name");
            String columnKey = resultSet.getString("column_key");
            String dataType = resultSet.getString("data_type");
            boolean isNullable = resultSet.getBoolean("is_nullable");
            int length = resultSet.getInt("character_maximum_length");
            String columnComment = resultSet.getString("column_comment");
            FieldInfo fieldInfo = new FieldInfo();
            if(StringUtils.isNotBlank(columnKey) || columnKey.equalsIgnoreCase("PRI")){
                fieldInfo.setKey(true);
            }
            if (StringUtils.isNotBlank(columnName) ) {
                fieldInfo.setColumn(columnName);
            }
            if(StringUtils.isNotBlank(dataType)){
                fieldInfo.setJdbcType(dataType);
            }
            fieldInfo.setNull(isNullable);
            fieldInfo.setLength(length);
            if(StringUtils.isNotBlank(columnComment)){
                fieldInfo.setComment(columnComment);
            }
            fieldInfoList.add(fieldInfo);
        }
        return fieldInfoList;
    }

    /**
     * 初始化用于存储各种操作表结构的容器
     *
     * @return 初始化map
     */
    private Map<String, Map<String, TableInfo>> initTableMap() {
        Map<String, Map<String, TableInfo>> baseTableMap = new HashMap<>(2);
        // 1.用于存需要创建的表名+（字段结构/表信息）
        baseTableMap.put(Constants.NEW_TABLE_MAP, new HashMap<>(2));
        // 2.用于存需要更新字段类型等的表名+结构
        baseTableMap.put(Constants.MODIFY_TABLE_MAP, new HashMap<>(2));
        // 3.用于存需要增加字段的表名+结构
        baseTableMap.put(Constants.ADD_TABLE_MAP, new HashMap<>(2));
        // 4.用于存需要删除字段的表名+结构
        baseTableMap.put(Constants.REMOVE_TABLE_MAP, new HashMap<>(2));

        return baseTableMap;
    }

    @Override
    public String createTableSql(final String schema, final TableInfo tableInfo) {
        StringBuilder sb = new StringBuilder();
        sb.append("create table ");
        sb.append(BACKTICK).append(schema).append(BACKTICK).append(DOT);
        sb.append(BACKTICK).append(tableInfo.getTableName()).append(BACKTICK);
        sb.append(LEFT_BRACKET);
        sb.append(RETURN);
        List<FieldInfo> fieldInfoList = tableInfo.getFieldInfoList();
        List<FieldInfo> collect = fieldInfoList.stream().filter(item -> !item.isKey()).toList();
        List<FieldInfo> isKey = fieldInfoList.stream().filter(FieldInfo::isKey).toList();
        if(CollUtil.isEmpty(isKey)){
            throw new RuntimeException("table = "+schema+"."+tableInfo.getTableName()+" 没有主键 ");
        }

        // 先拼接不是主键的；
        for (FieldInfo fieldInfo : collect) {
            getColumnInfo(fieldInfo,sb);
            sb.append(COMMA);
            sb.append(RETURN);
        }
        // 拼接主键
        String primaryKey = "";
        for (FieldInfo item : isKey) {
            sb.append(BACKTICK).append(item.getColumn()).append(BACKTICK);
            if (item.isNeedLength()) {
                sb.append(StringPool.SPACE).append(item.getJdbcType());
                sb.append(LEFT_BRACKET).append(item.getLength()).append(RIGHT_BRACKET);
            } else {
                sb.append(StringPool.SPACE).append(item.getJdbcType());
            }

            sb.append(" NOT NULL");
            if (item.isAutoIncrement()) {
                // 自增
                sb.append(" AUTO_INCREMENT ");
            }
            if (StringUtils.isNotBlank(item.getComment())) {
                sb.append(" COMMENT ").append(SINGLE_QUOTE).append(item.getComment()).append(SINGLE_QUOTE);
            }
            sb.append(COMMA);
            if (StringUtils.isBlank(primaryKey)) {
                primaryKey = item.getColumn();
            }else{
                primaryKey += COMMA+item.getColumn();
            }
            sb.append(RETURN);
        }
        sb.append(" PRIMARY KEY ").append(LEFT_BRACKET).append(BACKTICK).append(primaryKey).append(BACKTICK).append(RIGHT_BRACKET);
        sb.append(RIGHT_BRACKET);
        sb.append(RETURN);
        sb.append(" ENGINE = ").append((String) tableInfo.getExtraDataByKey(Constants.TABLE_ENGINE_KEY));
        sb.append(RETURN);
        sb.append(" DEFAULT CHARSET = ").append((String) tableInfo.getExtraDataByKey(Constants.TABLE_COLLATION_KEY));
        if(StringUtils.isNotBlank(tableInfo.getComment())){
            sb.append(" COMMENT=").append(SINGLE_QUOTE).append(tableInfo.getComment()).append(SINGLE_QUOTE);
        }
        return sb.toString();
    }

    @Override
    public String addTableColumnSql(final String schema, final String tableName, final FieldInfo fieldInfo) {
        // 判断字段长度 等信息
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(schema).append(DOT).append(tableName).append(" add ");
        return getColumnInfo(fieldInfo, sb);
    }

    @Override
    public String deleteTableColumnSql(final String schema, final String tableName, final String columnName) {
        return String.format("alter table %s.%s drop `%s`", schema, tableName, columnName);
    }

    @Override
    public String modifyTableColumnSql(final String schema, final String tableName, final FieldInfo fieldInfo) {
        // 判断字段长度 等信息
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(schema).append(DOT).append(tableName).append(" modify ");
        return getColumnInfo(fieldInfo, sb);
    }
    private static String getColumnInfo(FieldInfo fieldInfo, StringBuilder sb) {
        sb.append(BACKTICK).append(fieldInfo.getColumn()).append(BACKTICK);
        if (fieldInfo.isNeedLength()) {
            sb.append(SPACE).append(fieldInfo.getJdbcType());
            sb.append(LEFT_BRACKET).append(fieldInfo.getLength()).append(RIGHT_BRACKET);
        }else {
            sb.append(SPACE).append(fieldInfo.getJdbcType());
        }
        // 判断字段是不是允许为空
        if(fieldInfo.isKey()){
            sb.append(" NOT NULL ");
            if(fieldInfo.isAutoIncrement()){
                sb.append(" AUTO_INCREMENT ");
            }
        }else {
            if(fieldInfo.isNull() ){
                // 允许
                sb.append(" DEFAULT NULL ");
            }else{
                sb.append(" NOT NULL ");
            }
        }
        if(StringUtils.isNotBlank(fieldInfo.getComment())){
            sb.append(" COMMENT ").append(SINGLE_QUOTE).append(fieldInfo.getComment()).append(SINGLE_QUOTE);
        }
        return sb.toString();
    }

    @Override
    @SneakyThrows(value = SQLException.class)
    public void initConnection(String datasource) {
        String[] datasourceSplit = datasource.split(StringPool.COMMA);
        if (datasourceSplit.length != 3) {
            log.error("<错误的配置； {};正确格式 [dbUser,dbPwd,dbUrl]>", datasource);
        }
        String dbUrl = datasourceSplit[2];
        connectionList.add(DriverManager.getConnection(dbUrl, datasourceSplit[0], datasourceSplit[1]));

    }

    @Override
    public String getDropTableSql(String schema, String tableName) {
        return "drop table " + schema + "." + tableName;
    }

    @Override
    public String checkTableSql(String schema, String tableName) {
        return "select table_schema,table_name from information_schema.tables where table_name = '" + tableName + "' and table_schema = '" + schema + "'";
    }

    @Override
    public String getTableColumnSql(String schema, String tableName) {
        return "select column_key,column_name,data_type,is_nullable,character_maximum_length,column_comment from information_schema.columns where table_name = '" + tableName + "' and table_schema = '" + schema + "'";
    }

    @Override
    public DbType getDbType() {
        return DbType.MYSQL;
    }

    @Override
    public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
        this.beanFactory = beanFactory;
    }

    @Override
    public void setEnvironment(Environment environment) {
        this.environment = environment;
    }
}